﻿CREATE PROCEDURE [dbo].[spAlarm_BadPLE]
(
	@minimumGoodPLEPercent		int		= 90,
	@startDate					date	= NULL,
	@endDate					date	= NULL
)
AS

--- Ensure that there is a value for both start and end date
IF @startDate IS NULL AND @endDate IS NULL
BEGIN
	SET	@endDate = DATEADD(dd, 1, (CAST(GETDATE() as date)))
	SET @startDate = DATEADD(month, DATEDIFF(month, 0, @endDate), 0)
END
ELSE IF @startDate IS NULL
	SET @startDate = DATEADD(month, DATEDIFF(month, 0, @endDate), 0)
ELSE IF @endDate IS NULL
	SET	@endDate = DATEADD(dd, 1, @startDate)
ELSE IF @endDate IS NOT NULL
	SET	@endDate = DATEADD(dd, 1, @endDate) --- adding a day so that the requested date is part of the results

;WITH ple AS (
	SELECT		[page_life_expectancy],
				[minimum_recommended_page_life_expectancy],
				(CASE WHEN [page_life_expectancy] >= [minimum_recommended_page_life_expectancy] THEN 1 ELSE 0 END) [goodPLE],
				t2.[ssd_sqlserver_name_ro]
	FROM		[dbo].[vwWH_Perfmon] t1
	JOIN		[dbo].[tblMSX_server_discovery] t2 ON t1.[server_ssd_id] = t2.[ssd_id]
				AND t2.[ssd_is_disabled] = 0
	WHERE		t1.[date_inserted] >= @startDate
				AND t1.[date_inserted] < @endDate
)
SELECT		[ssd_sqlserver_name_ro],
			SUM([goodPLE]) [goodPLE],
			COUNT(*) [totalRecords],
			CAST((1.0 * SUM([goodPLE])) / COUNT(*) * 100 as int) [percentageGoodPLE]
FROM		ple
GROUP BY	[ssd_sqlserver_name_ro]
HAVING		CAST((1.0 * SUM([goodPLE])) / COUNT(*) * 100 as int) < @minimumGoodPLEPercent
ORDER BY	CAST((1.0 * SUM([goodPLE])) / COUNT(*) * 100 as int),
			[ssd_sqlserver_name_ro]